"""empty message

Revision ID: dbb9670cd902
Revises: f63b9b9b645b
Create Date: 2017-07-31 02:20:34.268384

"""

from alembic import op
import sqlalchemy as sa
import sqlalchemy_utils
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = 'dbb9670cd902'
down_revision = 'f63b9b9b645b'


class ReplaceableObject(object):
    def __init__(self, name, sqltext):
        self.name = name
        self.sqltext = sqltext

is_numeric = ReplaceableObject(
    "isnumeric(text)",
    """
    RETURNS BOOLEAN AS $$
    DECLARE
        x NUMERIC;
    BEGIN
        x = $1::integer;
        RETURN TRUE;
    EXCEPTION WHEN others THEN
        RETURN FALSE;
    END;
    $$
    STRICT
    LANGUAGE plpgsql IMMUTABLE;
    """)


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_or_replace_sp(is_numeric)
    op.execute('UPDATE sponsors SET level=NULL FROM (SELECT isnumeric(level) as is_numeric_level FROM sponsors) AS subquery WHERE subquery.is_numeric_level is FALSE ')
    op.execute('ALTER TABLE sponsors ALTER COLUMN level TYPE integer USING (trim(level)::integer)')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_sp(is_numeric)
    op.alter_column('sponsors', 'level',
                    existing_type=sa.Integer(),
                    type_=sa.VARCHAR(),
                    existing_nullable=True)
    # ### end Alembic commands ###
